﻿using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace MaQiu.SqlExecute.DAL
{
    internal class SqlQuery
    {
        private readonly DbCommand _cmd = SqlConnection.Provider.GetCommand();

        public SqlQuery(string sql) : this(sql, null)
        {
        }

        public SqlQuery(string sql, DbParameter[] param)
        {
            _cmd.CommandText = sql;
            if (param != null)
            {
                _cmd.Parameters.AddRange(param);
            }
        }

        public SqlQuery(string sql, object param)
        {
            _cmd.CommandText = sql;
            if (param != null)
            {
                _cmd.Parameters.AddRange(DbHepler.GetParameters(param));
            }
        }

        public DbCommand GetCommand()
        {
            return _cmd;
        }

        public static SqlQuery Create(string sql, object param)
        {
            return new SqlQuery(sql, DbHepler.GetParameters(param));
        }

        public static SqlQuery Create(string sql)
        {
            return new SqlQuery(sql, null);
        }

        public DataTable GetDataTable()
        {
            using (var scope = new SqlConnection())
            {
                return scope.GetConnection().ExecuteCommand(_cmd, delegate (DbCommand cmd)
                {
                    DataTable result;
                    using (var reader = cmd.ExecuteReader())
                    {
                        var dataSet = new DataSet { EnforceConstraints = false };
                        var dataTable = new DataTable("_tb");
                        dataSet.Tables.Add(dataTable);
                        dataTable.Load(reader);
                        result = dataTable;
                    }
                    return result;
                });
            }
        }

        public int ExecuteNonQuery()
        {
            using (var scope = new SqlConnection())
            {
                if (SqlConnection.IsSingleThreadExec)
                {
                    lock (SqlConnection.LockObject)
                    {
                        return scope.GetConnection().ExecuteCommand(_cmd, cmd => cmd.ExecuteNonQuery());
                    }
                }
                else
                {
                    return scope.GetConnection().ExecuteCommand(_cmd, cmd => cmd.ExecuteNonQuery());
                }
            }
        }

        public T ExecuteScalar<T>()
        {
            using (var scope = new SqlConnection())
            {
                return scope.GetConnection().ExecuteCommand(_cmd, delegate (DbCommand cmd)
                {
                    var result = cmd.ExecuteScalar();
                    return DbHepler.ConvertType<T>(result);
                });
            }
        }

        public List<T> ExecuteScalarList<T>()
        {
            using (var scope = new SqlConnection())
            {
                return scope.GetConnection().ExecuteCommand(_cmd, delegate (DbCommand cmd)
                {
                    var list = new List<T>();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(DbHepler.ConvertType<T>(reader[0]));
                        }
                    }
                    return list;
                });
            }
        }

        public T ToSingle<T>() where T : class, new()
        {
            using (var scope = new SqlConnection())
            {
                return scope.GetConnection().ExecuteCommand(_cmd, delegate (DbCommand cmd)
                {
                    T result;
                    using (var reader = cmd.ExecuteReader())
                    {
                        result = reader.Read() ? DbHepler.DataReaderToSingle<T>(reader) : null;
                    }
                    return result;
                });
            }
        }

        public List<T> ToList<T>() where T : class, new()
        {
            using (var scope = new SqlConnection())
            {
                return scope.GetConnection().ExecuteCommand(_cmd, delegate (DbCommand cmd)
                {
                    var result = new List<T>();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result.Add(DbHepler.DataReaderToSingle<T>(reader));
                        }
                    }
                    return result;
                });
            }
        }
    }
}
